﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DataTable = System.Data.DataTable;

namespace ClassLibrary_MultiLanguage
{
    public static class SaveExcel
    {
        #region DateGridView导出到csv格式的Excel
        /// <summary>
        /// 常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。
        /// </summary>
        /// <remarks>
        /// using System.IO;
        /// </remarks>
        /// <param name="dgv"></param>
        public static void DataGridViewToExcel(DataGridView dgv)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = "";
                try
                {
                    //写入列标题
                    for (int i = 0; i < dgv.ColumnCount; i++)
                    {
                        if (i > 0)
                        {
                            columnTitle += "\t";
                        }
                        columnTitle += dgv.Columns[i].HeaderText;
                    }
                    sw.WriteLine(columnTitle);

                    //写入列内容
                    for (int j = 0; j < dgv.Rows.Count; j++)
                    {
                        string columnValue = "";
                        for (int k = 0; k < dgv.Columns.Count; k++)
                        {
                            if (k > 0)
                            {
                                columnValue += "\t";
                            }
                            if (dgv.Rows[j].Cells[k].Value == null)
                                columnValue += "";
                            else
                                columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                    MessageBox.Show("文件转换完成...");
                }
            }
        }
        #endregion

        #region 从Excel中读文件到datagridView中
        public static void ReadExcel(DataGridView dataGridView, String fileName)
        {
            System.Data.DataTable dataTable = new DataTable();        
            dataTable = new System.Data.DataTable();
            OpenFileDialog ofd = new OpenFileDialog();
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook;
            Microsoft.Office.Interop.Excel.Worksheet worksheet;
            object oMissing = System.Reflection.Missing.Value;//相当null
            workbook = excel.Workbooks.Open(fileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            worksheet = (Worksheet)workbook.Worksheets[1];
            int rowCount = worksheet.UsedRange.Rows.Count;
            Console.WriteLine(rowCount);
            int colCount = worksheet.UsedRange.Columns.Count;
            Microsoft.Office.Interop.Excel.Range range1;
            for (int i = 0; i < colCount; i++)
            {
                range1 = worksheet.Range[worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]];
                dataTable.Columns.Add(range1.Value2.ToString());
            }
            for (int j = 1; j < rowCount; j++)
            {
                DataRow dr = dataTable.NewRow();
                for (int i = 0; i < colCount; i++)
                {
                    range1 = worksheet.Range[worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]];
                    dr[i] = range1.Value2 == null ? null : range1.Value2;
                }
                dataTable.Rows.Add(dr);
            }
            dataGridView.DataSource = null;
            dataGridView.DataSource = dataTable;
            dataGridView.Rows[0].Selected = false;
            dataGridView.ClearSelection();
            excel.Quit();
        }
        #endregion
    }
}
